{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b231e06a",
   "metadata": {},
   "source": [
    "## Preparing Zipline Backtest Results for Pyfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "232efb9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pyfolio as pf\n",
    "from IPython.display import Markdown, display\n",
    "from openbb import obb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "337e1621",
   "metadata": {},
   "outputs": [],
   "source": [
    "obb.user.preferences.output_type = \"dataframe\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53124e76",
   "metadata": {},
   "source": [
    "Load the mean reversion performance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "697c10da",
   "metadata": {},
   "outputs": [],
   "source": [
    "perf = pd.read_pickle(\"mean_reversion.pickle\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9db0b5d9",
   "metadata": {},
   "source": [
    "Display the performance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ede6139d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>period_open</th>\n",
       "      <th>period_close</th>\n",
       "      <th>starting_value</th>\n",
       "      <th>ending_value</th>\n",
       "      <th>starting_cash</th>\n",
       "      <th>ending_cash</th>\n",
       "      <th>returns</th>\n",
       "      <th>portfolio_value</th>\n",
       "      <th>longs_count</th>\n",
       "      <th>shorts_count</th>\n",
       "      <th>...</th>\n",
       "      <th>treasury_period_return</th>\n",
       "      <th>trading_days</th>\n",
       "      <th>period_label</th>\n",
       "      <th>algorithm_period_return</th>\n",
       "      <th>algo_volatility</th>\n",
       "      <th>benchmark_period_return</th>\n",
       "      <th>benchmark_volatility</th>\n",
       "      <th>alpha</th>\n",
       "      <th>beta</th>\n",
       "      <th>sharpe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-04 21:00:00+00:00</th>\n",
       "      <td>2016-01-04 14:31:00+00:00</td>\n",
       "      <td>2016-01-04 21:00:00+00:00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100000.00000</td>\n",
       "      <td>100000.00000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>100000.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-05 21:00:00+00:00</th>\n",
       "      <td>2016-01-05 14:31:00+00:00</td>\n",
       "      <td>2016-01-05 21:00:00+00:00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1977.80</td>\n",
       "      <td>100000.00000</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>-1.650000e-07</td>\n",
       "      <td>99999.98350</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01</td>\n",
       "      <td>-1.650000e-07</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>0.002012</td>\n",
       "      <td>0.022588</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.000082</td>\n",
       "      <td>-11.224972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-06 21:00:00+00:00</th>\n",
       "      <td>2016-01-06 14:31:00+00:00</td>\n",
       "      <td>2016-01-06 21:00:00+00:00</td>\n",
       "      <td>-1977.80</td>\n",
       "      <td>-1986.16</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>-8.360001e-05</td>\n",
       "      <td>99991.62350</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-01</td>\n",
       "      <td>-8.376500e-05</td>\n",
       "      <td>0.000765</td>\n",
       "      <td>-0.011130</td>\n",
       "      <td>0.130408</td>\n",
       "      <td>-0.001603</td>\n",
       "      <td>0.005824</td>\n",
       "      <td>-9.192298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-07 21:00:00+00:00</th>\n",
       "      <td>2016-01-07 14:31:00+00:00</td>\n",
       "      <td>2016-01-07 21:00:00+00:00</td>\n",
       "      <td>-1986.16</td>\n",
       "      <td>-1983.30</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>2.860240e-05</td>\n",
       "      <td>99994.48350</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2016-01</td>\n",
       "      <td>-5.516500e-05</td>\n",
       "      <td>0.000769</td>\n",
       "      <td>-0.034566</td>\n",
       "      <td>0.191144</td>\n",
       "      <td>-0.003779</td>\n",
       "      <td>-0.000142</td>\n",
       "      <td>-4.517139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-08 21:00:00+00:00</th>\n",
       "      <td>2016-01-08 14:31:00+00:00</td>\n",
       "      <td>2016-01-08 21:00:00+00:00</td>\n",
       "      <td>-1983.30</td>\n",
       "      <td>-1986.38</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>101977.78350</td>\n",
       "      <td>-3.080170e-05</td>\n",
       "      <td>99991.40350</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-01</td>\n",
       "      <td>-8.596500e-05</td>\n",
       "      <td>0.000677</td>\n",
       "      <td>-0.045030</td>\n",
       "      <td>0.166229</td>\n",
       "      <td>-0.004494</td>\n",
       "      <td>-0.000074</td>\n",
       "      <td>-6.398478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-22 21:00:00+00:00</th>\n",
       "      <td>2017-12-22 14:31:00+00:00</td>\n",
       "      <td>2017-12-22 21:00:00+00:00</td>\n",
       "      <td>5747.17</td>\n",
       "      <td>5763.90</td>\n",
       "      <td>95142.41300</td>\n",
       "      <td>95142.41300</td>\n",
       "      <td>1.658249e-04</td>\n",
       "      <td>100906.31300</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>499</td>\n",
       "      <td>2017-12</td>\n",
       "      <td>9.063130e-03</td>\n",
       "      <td>0.015238</td>\n",
       "      <td>0.333231</td>\n",
       "      <td>0.103667</td>\n",
       "      <td>0.001123</td>\n",
       "      <td>0.023565</td>\n",
       "      <td>0.306637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-26 21:00:00+00:00</th>\n",
       "      <td>2017-12-26 14:31:00+00:00</td>\n",
       "      <td>2017-12-26 21:00:00+00:00</td>\n",
       "      <td>5763.90</td>\n",
       "      <td>5696.27</td>\n",
       "      <td>95142.41300</td>\n",
       "      <td>95142.41300</td>\n",
       "      <td>-6.702257e-04</td>\n",
       "      <td>100838.68300</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>500</td>\n",
       "      <td>2017-12</td>\n",
       "      <td>8.386830e-03</td>\n",
       "      <td>0.015230</td>\n",
       "      <td>0.331820</td>\n",
       "      <td>0.103569</td>\n",
       "      <td>0.000788</td>\n",
       "      <td>0.023615</td>\n",
       "      <td>0.283994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>2017-12-27 14:31:00+00:00</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>5696.27</td>\n",
       "      <td>64.69</td>\n",
       "      <td>95142.41300</td>\n",
       "      <td>100840.72425</td>\n",
       "      <td>6.617624e-04</td>\n",
       "      <td>100905.41425</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>501</td>\n",
       "      <td>2017-12</td>\n",
       "      <td>9.054142e-03</td>\n",
       "      <td>0.015222</td>\n",
       "      <td>0.332873</td>\n",
       "      <td>0.103466</td>\n",
       "      <td>0.001109</td>\n",
       "      <td>0.023621</td>\n",
       "      <td>0.305450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-28 21:00:00+00:00</th>\n",
       "      <td>2017-12-28 14:31:00+00:00</td>\n",
       "      <td>2017-12-28 21:00:00+00:00</td>\n",
       "      <td>64.69</td>\n",
       "      <td>90.28</td>\n",
       "      <td>100840.72425</td>\n",
       "      <td>100840.72425</td>\n",
       "      <td>2.536038e-04</td>\n",
       "      <td>100931.00425</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>502</td>\n",
       "      <td>2017-12</td>\n",
       "      <td>9.310042e-03</td>\n",
       "      <td>0.015208</td>\n",
       "      <td>0.335317</td>\n",
       "      <td>0.103366</td>\n",
       "      <td>0.001211</td>\n",
       "      <td>0.023633</td>\n",
       "      <td>0.313499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-29 21:00:00+00:00</th>\n",
       "      <td>2017-12-29 14:31:00+00:00</td>\n",
       "      <td>2017-12-29 21:00:00+00:00</td>\n",
       "      <td>90.28</td>\n",
       "      <td>76.95</td>\n",
       "      <td>100840.72425</td>\n",
       "      <td>100840.72425</td>\n",
       "      <td>-1.320704e-04</td>\n",
       "      <td>100917.67425</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>503</td>\n",
       "      <td>2017-12</td>\n",
       "      <td>9.176742e-03</td>\n",
       "      <td>0.015193</td>\n",
       "      <td>0.328396</td>\n",
       "      <td>0.103344</td>\n",
       "      <td>0.001203</td>\n",
       "      <td>0.023637</td>\n",
       "      <td>0.308825</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>503 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        period_open              period_close  \\\n",
       "2016-01-04 21:00:00+00:00 2016-01-04 14:31:00+00:00 2016-01-04 21:00:00+00:00   \n",
       "2016-01-05 21:00:00+00:00 2016-01-05 14:31:00+00:00 2016-01-05 21:00:00+00:00   \n",
       "2016-01-06 21:00:00+00:00 2016-01-06 14:31:00+00:00 2016-01-06 21:00:00+00:00   \n",
       "2016-01-07 21:00:00+00:00 2016-01-07 14:31:00+00:00 2016-01-07 21:00:00+00:00   \n",
       "2016-01-08 21:00:00+00:00 2016-01-08 14:31:00+00:00 2016-01-08 21:00:00+00:00   \n",
       "...                                             ...                       ...   \n",
       "2017-12-22 21:00:00+00:00 2017-12-22 14:31:00+00:00 2017-12-22 21:00:00+00:00   \n",
       "2017-12-26 21:00:00+00:00 2017-12-26 14:31:00+00:00 2017-12-26 21:00:00+00:00   \n",
       "2017-12-27 21:00:00+00:00 2017-12-27 14:31:00+00:00 2017-12-27 21:00:00+00:00   \n",
       "2017-12-28 21:00:00+00:00 2017-12-28 14:31:00+00:00 2017-12-28 21:00:00+00:00   \n",
       "2017-12-29 21:00:00+00:00 2017-12-29 14:31:00+00:00 2017-12-29 21:00:00+00:00   \n",
       "\n",
       "                           starting_value  ending_value  starting_cash  \\\n",
       "2016-01-04 21:00:00+00:00            0.00          0.00   100000.00000   \n",
       "2016-01-05 21:00:00+00:00            0.00      -1977.80   100000.00000   \n",
       "2016-01-06 21:00:00+00:00        -1977.80      -1986.16   101977.78350   \n",
       "2016-01-07 21:00:00+00:00        -1986.16      -1983.30   101977.78350   \n",
       "2016-01-08 21:00:00+00:00        -1983.30      -1986.38   101977.78350   \n",
       "...                                   ...           ...            ...   \n",
       "2017-12-22 21:00:00+00:00         5747.17       5763.90    95142.41300   \n",
       "2017-12-26 21:00:00+00:00         5763.90       5696.27    95142.41300   \n",
       "2017-12-27 21:00:00+00:00         5696.27         64.69    95142.41300   \n",
       "2017-12-28 21:00:00+00:00           64.69         90.28   100840.72425   \n",
       "2017-12-29 21:00:00+00:00           90.28         76.95   100840.72425   \n",
       "\n",
       "                            ending_cash       returns  portfolio_value  \\\n",
       "2016-01-04 21:00:00+00:00  100000.00000  0.000000e+00     100000.00000   \n",
       "2016-01-05 21:00:00+00:00  101977.78350 -1.650000e-07      99999.98350   \n",
       "2016-01-06 21:00:00+00:00  101977.78350 -8.360001e-05      99991.62350   \n",
       "2016-01-07 21:00:00+00:00  101977.78350  2.860240e-05      99994.48350   \n",
       "2016-01-08 21:00:00+00:00  101977.78350 -3.080170e-05      99991.40350   \n",
       "...                                 ...           ...              ...   \n",
       "2017-12-22 21:00:00+00:00   95142.41300  1.658249e-04     100906.31300   \n",
       "2017-12-26 21:00:00+00:00   95142.41300 -6.702257e-04     100838.68300   \n",
       "2017-12-27 21:00:00+00:00  100840.72425  6.617624e-04     100905.41425   \n",
       "2017-12-28 21:00:00+00:00  100840.72425  2.536038e-04     100931.00425   \n",
       "2017-12-29 21:00:00+00:00  100840.72425 -1.320704e-04     100917.67425   \n",
       "\n",
       "                           longs_count  shorts_count  ...  \\\n",
       "2016-01-04 21:00:00+00:00            0             0  ...   \n",
       "2016-01-05 21:00:00+00:00            0             1  ...   \n",
       "2016-01-06 21:00:00+00:00            0             1  ...   \n",
       "2016-01-07 21:00:00+00:00            0             1  ...   \n",
       "2016-01-08 21:00:00+00:00            0             1  ...   \n",
       "...                                ...           ...  ...   \n",
       "2017-12-22 21:00:00+00:00            4             1  ...   \n",
       "2017-12-26 21:00:00+00:00            4             1  ...   \n",
       "2017-12-27 21:00:00+00:00            4             4  ...   \n",
       "2017-12-28 21:00:00+00:00            4             4  ...   \n",
       "2017-12-29 21:00:00+00:00            4             4  ...   \n",
       "\n",
       "                           treasury_period_return  trading_days  period_label  \\\n",
       "2016-01-04 21:00:00+00:00                     0.0             1       2016-01   \n",
       "2016-01-05 21:00:00+00:00                     0.0             2       2016-01   \n",
       "2016-01-06 21:00:00+00:00                     0.0             3       2016-01   \n",
       "2016-01-07 21:00:00+00:00                     0.0             4       2016-01   \n",
       "2016-01-08 21:00:00+00:00                     0.0             5       2016-01   \n",
       "...                                           ...           ...           ...   \n",
       "2017-12-22 21:00:00+00:00                     0.0           499       2017-12   \n",
       "2017-12-26 21:00:00+00:00                     0.0           500       2017-12   \n",
       "2017-12-27 21:00:00+00:00                     0.0           501       2017-12   \n",
       "2017-12-28 21:00:00+00:00                     0.0           502       2017-12   \n",
       "2017-12-29 21:00:00+00:00                     0.0           503       2017-12   \n",
       "\n",
       "                           algorithm_period_return  algo_volatility  \\\n",
       "2016-01-04 21:00:00+00:00             0.000000e+00              NaN   \n",
       "2016-01-05 21:00:00+00:00            -1.650000e-07         0.000002   \n",
       "2016-01-06 21:00:00+00:00            -8.376500e-05         0.000765   \n",
       "2016-01-07 21:00:00+00:00            -5.516500e-05         0.000769   \n",
       "2016-01-08 21:00:00+00:00            -8.596500e-05         0.000677   \n",
       "...                                            ...              ...   \n",
       "2017-12-22 21:00:00+00:00             9.063130e-03         0.015238   \n",
       "2017-12-26 21:00:00+00:00             8.386830e-03         0.015230   \n",
       "2017-12-27 21:00:00+00:00             9.054142e-03         0.015222   \n",
       "2017-12-28 21:00:00+00:00             9.310042e-03         0.015208   \n",
       "2017-12-29 21:00:00+00:00             9.176742e-03         0.015193   \n",
       "\n",
       "                           benchmark_period_return benchmark_volatility  \\\n",
       "2016-01-04 21:00:00+00:00                 0.000000                  NaN   \n",
       "2016-01-05 21:00:00+00:00                 0.002012             0.022588   \n",
       "2016-01-06 21:00:00+00:00                -0.011130             0.130408   \n",
       "2016-01-07 21:00:00+00:00                -0.034566             0.191144   \n",
       "2016-01-08 21:00:00+00:00                -0.045030             0.166229   \n",
       "...                                            ...                  ...   \n",
       "2017-12-22 21:00:00+00:00                 0.333231             0.103667   \n",
       "2017-12-26 21:00:00+00:00                 0.331820             0.103569   \n",
       "2017-12-27 21:00:00+00:00                 0.332873             0.103466   \n",
       "2017-12-28 21:00:00+00:00                 0.335317             0.103366   \n",
       "2017-12-29 21:00:00+00:00                 0.328396             0.103344   \n",
       "\n",
       "                              alpha      beta     sharpe  \n",
       "2016-01-04 21:00:00+00:00       NaN       NaN        NaN  \n",
       "2016-01-05 21:00:00+00:00  0.000000 -0.000082 -11.224972  \n",
       "2016-01-06 21:00:00+00:00 -0.001603  0.005824  -9.192298  \n",
       "2016-01-07 21:00:00+00:00 -0.003779 -0.000142  -4.517139  \n",
       "2016-01-08 21:00:00+00:00 -0.004494 -0.000074  -6.398478  \n",
       "...                             ...       ...        ...  \n",
       "2017-12-22 21:00:00+00:00  0.001123  0.023565   0.306637  \n",
       "2017-12-26 21:00:00+00:00  0.000788  0.023615   0.283994  \n",
       "2017-12-27 21:00:00+00:00  0.001109  0.023621   0.305450  \n",
       "2017-12-28 21:00:00+00:00  0.001211  0.023633   0.313499  \n",
       "2017-12-29 21:00:00+00:00  0.001203  0.023637   0.308825  \n",
       "\n",
       "[503 rows x 39 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(perf)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2210e490",
   "metadata": {},
   "source": [
    "Extract returns, positions, and transactions from Zipline performance DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b0c35631",
   "metadata": {},
   "outputs": [],
   "source": [
    "returns, positions, transactions = pf.utils.extract_rets_pos_txn_from_zipline(perf)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2ba9409",
   "metadata": {},
   "source": [
    "Display the extracted returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8125356e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-01-04 00:00:00+00:00    0.000000e+00\n",
       "2016-01-05 00:00:00+00:00   -1.650000e-07\n",
       "2016-01-06 00:00:00+00:00   -8.360001e-05\n",
       "2016-01-07 00:00:00+00:00    2.860240e-05\n",
       "2016-01-08 00:00:00+00:00   -3.080170e-05\n",
       "                                 ...     \n",
       "2017-12-22 00:00:00+00:00    1.658249e-04\n",
       "2017-12-26 00:00:00+00:00   -6.702257e-04\n",
       "2017-12-27 00:00:00+00:00    6.617624e-04\n",
       "2017-12-28 00:00:00+00:00    2.536038e-04\n",
       "2017-12-29 00:00:00+00:00   -1.320704e-04\n",
       "Name: returns, Length: 503, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(returns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0b0f326",
   "metadata": {},
   "source": [
    "Rename position columns to stock symbols and cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f8f516e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "positions.columns = [col.symbol for col in positions.columns[:-1]] + [\"cash\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b9965b8",
   "metadata": {},
   "source": [
    "Display the extracted positions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e93e6b7b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AAL</th>\n",
       "      <th>AAPL</th>\n",
       "      <th>ABBV</th>\n",
       "      <th>ADBE</th>\n",
       "      <th>ADP</th>\n",
       "      <th>AET</th>\n",
       "      <th>AGN</th>\n",
       "      <th>AIG</th>\n",
       "      <th>ALXN</th>\n",
       "      <th>AMAT</th>\n",
       "      <th>...</th>\n",
       "      <th>UPS</th>\n",
       "      <th>USB</th>\n",
       "      <th>VRX</th>\n",
       "      <th>VZ</th>\n",
       "      <th>WBA</th>\n",
       "      <th>WDC</th>\n",
       "      <th>WFC</th>\n",
       "      <th>WFM</th>\n",
       "      <th>WMT</th>\n",
       "      <th>cash</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-05 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101977.78350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-06 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101977.78350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-07 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101977.78350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-08 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101977.78350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-11 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101977.78350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-22 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1926.22</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1968.03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1936.32</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95142.41300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-26 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1888.85</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1969.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1920.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95142.41300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1950.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100840.72425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-28 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1961.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100840.72425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-29 00:00:00+00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1941.44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100840.72425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493 rows × 135 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           AAL  AAPL  ABBV  ADBE  ADP  AET  AGN  AIG  ALXN  \\\n",
       "index                                                                        \n",
       "2016-01-05 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2016-01-06 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2016-01-07 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2016-01-08 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2016-01-11 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "...                        ...   ...   ...   ...  ...  ...  ...  ...   ...   \n",
       "2017-12-22 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2017-12-26 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2017-12-27 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2017-12-28 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "2017-12-29 00:00:00+00:00  0.0   0.0   0.0   0.0  0.0  0.0  0.0  0.0   0.0   \n",
       "\n",
       "                              AMAT  ...  UPS  USB  VRX       VZ  WBA      WDC  \\\n",
       "index                               ...                                         \n",
       "2016-01-05 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2016-01-06 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2016-01-07 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2016-01-08 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2016-01-11 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "...                            ...  ...  ...  ...  ...      ...  ...      ...   \n",
       "2017-12-22 00:00:00+00:00  1926.22  ...  0.0  0.0  0.0 -1968.03  0.0  1936.32   \n",
       "2017-12-26 00:00:00+00:00  1888.85  ...  0.0  0.0  0.0 -1969.14  0.0  1920.00   \n",
       "2017-12-27 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2017-12-28 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "2017-12-29 00:00:00+00:00     0.00  ...  0.0  0.0  0.0     0.00  0.0     0.00   \n",
       "\n",
       "                               WFC  WFM  WMT          cash  \n",
       "index                                                       \n",
       "2016-01-05 00:00:00+00:00     0.00  0.0  0.0  101977.78350  \n",
       "2016-01-06 00:00:00+00:00     0.00  0.0  0.0  101977.78350  \n",
       "2016-01-07 00:00:00+00:00     0.00  0.0  0.0  101977.78350  \n",
       "2016-01-08 00:00:00+00:00     0.00  0.0  0.0  101977.78350  \n",
       "2016-01-11 00:00:00+00:00     0.00  0.0  0.0  101977.78350  \n",
       "...                            ...  ...  ...           ...  \n",
       "2017-12-22 00:00:00+00:00     0.00  0.0  0.0   95142.41300  \n",
       "2017-12-26 00:00:00+00:00     0.00  0.0  0.0   95142.41300  \n",
       "2017-12-27 00:00:00+00:00 -1950.40  0.0  0.0  100840.72425  \n",
       "2017-12-28 00:00:00+00:00 -1961.60  0.0  0.0  100840.72425  \n",
       "2017-12-29 00:00:00+00:00 -1941.44  0.0  0.0  100840.72425  \n",
       "\n",
       "[493 rows x 135 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(positions)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc7b8735",
   "metadata": {},
   "source": [
    "Apply the symbol attribute to the transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "427af1f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions.symbol = transactions.symbol.apply(lambda s: s.symbol)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9e2453f",
   "metadata": {},
   "source": [
    "Display the extracted transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5f12c98f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>price</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>commission</th>\n",
       "      <th>dt</th>\n",
       "      <th>txn_dollars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-05 21:00:00+00:00</th>\n",
       "      <td>Equity(1228 [GMCR])</td>\n",
       "      <td>GMCR</td>\n",
       "      <td>89.90</td>\n",
       "      <td>6c0bfa6d54d1441baed02c6f0607a864</td>\n",
       "      <td>-22</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-05 21:00:00+00:00</td>\n",
       "      <td>1977.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-12 21:00:00+00:00</th>\n",
       "      <td>Equity(1228 [GMCR])</td>\n",
       "      <td>GMCR</td>\n",
       "      <td>90.42</td>\n",
       "      <td>17907b8052f64fe3a5bc66e9ed8b09fd</td>\n",
       "      <td>22</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-12 21:00:00+00:00</td>\n",
       "      <td>-1989.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-12 21:00:00+00:00</th>\n",
       "      <td>Equity(3105 [WMT])</td>\n",
       "      <td>WMT</td>\n",
       "      <td>63.62</td>\n",
       "      <td>23ddc217cfdd4a94b491013377386d8f</td>\n",
       "      <td>-31</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-12 21:00:00+00:00</td>\n",
       "      <td>1972.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-20 21:00:00+00:00</th>\n",
       "      <td>Equity(3105 [WMT])</td>\n",
       "      <td>WMT</td>\n",
       "      <td>60.84</td>\n",
       "      <td>69aed3b9780e4f5abbd7ecd1ddde5527</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-20 21:00:00+00:00</td>\n",
       "      <td>-1886.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-20 21:00:00+00:00</th>\n",
       "      <td>Equity(994 [ESRX])</td>\n",
       "      <td>ESRX</td>\n",
       "      <td>71.70</td>\n",
       "      <td>6d826744a2df47f5991903b16516fe45</td>\n",
       "      <td>27</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-01-20 21:00:00+00:00</td>\n",
       "      <td>-1935.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>Equity(2331 [PYPL])</td>\n",
       "      <td>PYPL</td>\n",
       "      <td>74.59</td>\n",
       "      <td>9d1d861c73eb4a87ad9880c6ec940a82</td>\n",
       "      <td>27</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>-2013.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>Equity(606 [CMCSA])</td>\n",
       "      <td>CMCSA</td>\n",
       "      <td>40.41</td>\n",
       "      <td>2e36bcbdf6484592bf63b14ad9942aa3</td>\n",
       "      <td>-49</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>1980.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>Equity(1063 [FDX])</td>\n",
       "      <td>FDX</td>\n",
       "      <td>250.03</td>\n",
       "      <td>ba5adcf1fd4f40db95eebd864b9aead5</td>\n",
       "      <td>-8</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>2000.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>Equity(2945 [UNP])</td>\n",
       "      <td>UNP</td>\n",
       "      <td>136.32</td>\n",
       "      <td>7a9cda6bb33a4f3b9ec3ed3e718c4a0b</td>\n",
       "      <td>-14</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>1908.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-27 21:00:00+00:00</th>\n",
       "      <td>Equity(3077 [WFC])</td>\n",
       "      <td>WFC</td>\n",
       "      <td>60.95</td>\n",
       "      <td>2efd159050db4dd4bc144e9891323cdd</td>\n",
       "      <td>-32</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-12-27 21:00:00+00:00</td>\n",
       "      <td>1950.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>550 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           sid symbol   price  \\\n",
       "2016-01-05 21:00:00+00:00  Equity(1228 [GMCR])   GMCR   89.90   \n",
       "2016-01-12 21:00:00+00:00  Equity(1228 [GMCR])   GMCR   90.42   \n",
       "2016-01-12 21:00:00+00:00   Equity(3105 [WMT])    WMT   63.62   \n",
       "2016-01-20 21:00:00+00:00   Equity(3105 [WMT])    WMT   60.84   \n",
       "2016-01-20 21:00:00+00:00   Equity(994 [ESRX])   ESRX   71.70   \n",
       "...                                        ...    ...     ...   \n",
       "2017-12-27 21:00:00+00:00  Equity(2331 [PYPL])   PYPL   74.59   \n",
       "2017-12-27 21:00:00+00:00  Equity(606 [CMCSA])  CMCSA   40.41   \n",
       "2017-12-27 21:00:00+00:00   Equity(1063 [FDX])    FDX  250.03   \n",
       "2017-12-27 21:00:00+00:00   Equity(2945 [UNP])    UNP  136.32   \n",
       "2017-12-27 21:00:00+00:00   Equity(3077 [WFC])    WFC   60.95   \n",
       "\n",
       "                                                   order_id  amount  \\\n",
       "2016-01-05 21:00:00+00:00  6c0bfa6d54d1441baed02c6f0607a864     -22   \n",
       "2016-01-12 21:00:00+00:00  17907b8052f64fe3a5bc66e9ed8b09fd      22   \n",
       "2016-01-12 21:00:00+00:00  23ddc217cfdd4a94b491013377386d8f     -31   \n",
       "2016-01-20 21:00:00+00:00  69aed3b9780e4f5abbd7ecd1ddde5527      31   \n",
       "2016-01-20 21:00:00+00:00  6d826744a2df47f5991903b16516fe45      27   \n",
       "...                                                     ...     ...   \n",
       "2017-12-27 21:00:00+00:00  9d1d861c73eb4a87ad9880c6ec940a82      27   \n",
       "2017-12-27 21:00:00+00:00  2e36bcbdf6484592bf63b14ad9942aa3     -49   \n",
       "2017-12-27 21:00:00+00:00  ba5adcf1fd4f40db95eebd864b9aead5      -8   \n",
       "2017-12-27 21:00:00+00:00  7a9cda6bb33a4f3b9ec3ed3e718c4a0b     -14   \n",
       "2017-12-27 21:00:00+00:00  2efd159050db4dd4bc144e9891323cdd     -32   \n",
       "\n",
       "                          commission                        dt  txn_dollars  \n",
       "2016-01-05 21:00:00+00:00       None 2016-01-05 21:00:00+00:00      1977.80  \n",
       "2016-01-12 21:00:00+00:00       None 2016-01-12 21:00:00+00:00     -1989.24  \n",
       "2016-01-12 21:00:00+00:00       None 2016-01-12 21:00:00+00:00      1972.22  \n",
       "2016-01-20 21:00:00+00:00       None 2016-01-20 21:00:00+00:00     -1886.04  \n",
       "2016-01-20 21:00:00+00:00       None 2016-01-20 21:00:00+00:00     -1935.90  \n",
       "...                              ...                       ...          ...  \n",
       "2017-12-27 21:00:00+00:00       None 2017-12-27 21:00:00+00:00     -2013.93  \n",
       "2017-12-27 21:00:00+00:00       None 2017-12-27 21:00:00+00:00      1980.09  \n",
       "2017-12-27 21:00:00+00:00       None 2017-12-27 21:00:00+00:00      2000.24  \n",
       "2017-12-27 21:00:00+00:00       None 2017-12-27 21:00:00+00:00      1908.48  \n",
       "2017-12-27 21:00:00+00:00       None 2017-12-27 21:00:00+00:00      1950.40  \n",
       "\n",
       "[550 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(transactions)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d25c7651",
   "metadata": {},
   "source": [
    "Get the list of symbols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a655739a",
   "metadata": {},
   "outputs": [],
   "source": [
    "symbols = positions.columns[:-1].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66ca66f3",
   "metadata": {},
   "source": [
    "Get screener data for the symbols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "99b90ada",
   "metadata": {},
   "outputs": [],
   "source": [
    "screener_data = obb.equity.profile(symbols, provider=\"yfinance\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf08f8f5",
   "metadata": {},
   "source": [
    "Create a sector map from the screener data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "91d9424b",
   "metadata": {},
   "outputs": [],
   "source": [
    "sector_map = (\n",
    "    screener_data[[\"symbol\", \"sector\"]]\n",
    "    .set_index(\"symbol\")\n",
    "    .reindex(symbols)\n",
    "    .fillna(\"Unknown\")\n",
    "    .to_dict()[\"sector\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd97850b",
   "metadata": {},
   "source": [
    "Display the sector map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a66f10a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AAL': 'Industrials',\n",
       " 'AAPL': 'Technology',\n",
       " 'ABBV': 'Healthcare',\n",
       " 'ADBE': 'Technology',\n",
       " 'ADP': 'Industrials',\n",
       " 'AET': 'Unknown',\n",
       " 'AGN': 'Unknown',\n",
       " 'AIG': 'Financial Services',\n",
       " 'ALXN': 'Unknown',\n",
       " 'AMAT': 'Technology',\n",
       " 'AMGN': 'Healthcare',\n",
       " 'AMZN': 'Consumer Cyclical',\n",
       " 'ANTM': 'Unknown',\n",
       " 'ARIA': 'Unknown',\n",
       " 'ATVI': 'Unknown',\n",
       " 'AVGO': 'Technology',\n",
       " 'AXP': 'Financial Services',\n",
       " 'AZO': 'Consumer Cyclical',\n",
       " 'BA': 'Industrials',\n",
       " 'BBY': 'Consumer Cyclical',\n",
       " 'BCR': 'Unknown',\n",
       " 'BIDU': 'Communication Services',\n",
       " 'BIIB': 'Healthcare',\n",
       " 'BMY': 'Healthcare',\n",
       " 'BRK_B': 'Unknown',\n",
       " 'CCE': 'Unknown',\n",
       " 'CELG': 'Unknown',\n",
       " 'CHTR': 'Communication Services',\n",
       " 'CL': 'Consumer Defensive',\n",
       " 'CMCSA': 'Communication Services',\n",
       " 'CMG': 'Consumer Cyclical',\n",
       " 'COL': 'Unknown',\n",
       " 'COST': 'Consumer Defensive',\n",
       " 'CSCO': 'Technology',\n",
       " 'CSX': 'Industrials',\n",
       " 'CTSH': 'Technology',\n",
       " 'CVS': 'Healthcare',\n",
       " 'DAL': 'Industrials',\n",
       " 'DE': 'Industrials',\n",
       " 'DG': 'Consumer Defensive',\n",
       " 'DVN': 'Energy',\n",
       " 'EA': 'Communication Services',\n",
       " 'EBAY': 'Consumer Cyclical',\n",
       " 'EFX': 'Industrials',\n",
       " 'ESRX': 'Unknown',\n",
       " 'EXPE': 'Consumer Cyclical',\n",
       " 'F': 'Consumer Cyclical',\n",
       " 'FB': 'Unknown',\n",
       " 'FDX': 'Industrials',\n",
       " 'GE': 'Industrials',\n",
       " 'GILD': 'Healthcare',\n",
       " 'GM': 'Consumer Cyclical',\n",
       " 'GMCR': 'Unknown',\n",
       " 'GOOG': 'Communication Services',\n",
       " 'GS': 'Financial Services',\n",
       " 'HAL': 'Energy',\n",
       " 'HD': 'Consumer Cyclical',\n",
       " 'HON': 'Industrials',\n",
       " 'HUM': 'Healthcare',\n",
       " 'IBM': 'Technology',\n",
       " 'INCY': 'Healthcare',\n",
       " 'INTC': 'Technology',\n",
       " 'ISRG': 'Healthcare',\n",
       " 'JNJ': 'Healthcare',\n",
       " 'KMI': 'Energy',\n",
       " 'KO': 'Consumer Defensive',\n",
       " 'KR': 'Consumer Defensive',\n",
       " 'LLY': 'Healthcare',\n",
       " 'LMT': 'Industrials',\n",
       " 'LNKD': 'Unknown',\n",
       " 'LOW': 'Consumer Cyclical',\n",
       " 'LRCX': 'Technology',\n",
       " 'M': 'Consumer Cyclical',\n",
       " 'MA': 'Financial Services',\n",
       " 'MCD': 'Consumer Cyclical',\n",
       " 'MCK': 'Healthcare',\n",
       " 'MDLZ': 'Consumer Defensive',\n",
       " 'MDT': 'Healthcare',\n",
       " 'MJN': 'Unknown',\n",
       " 'MMM': 'Industrials',\n",
       " 'MO': 'Consumer Defensive',\n",
       " 'MON': 'Unknown',\n",
       " 'MRK': 'Healthcare',\n",
       " 'MRO': 'Energy',\n",
       " 'MS': 'Financial Services',\n",
       " 'MSFT': 'Technology',\n",
       " 'MU': 'Technology',\n",
       " 'MYL': 'Unknown',\n",
       " 'NFLX': 'Communication Services',\n",
       " 'NKE': 'Consumer Cyclical',\n",
       " 'NVDA': 'Technology',\n",
       " 'ORCL': 'Technology',\n",
       " 'ORLY': 'Consumer Cyclical',\n",
       " 'OXY': 'Energy',\n",
       " 'PANW': 'Technology',\n",
       " 'PCG': 'Utilities',\n",
       " 'PCLN': 'Unknown',\n",
       " 'PEP': 'Consumer Defensive',\n",
       " 'PFE': 'Healthcare',\n",
       " 'PG': 'Consumer Defensive',\n",
       " 'PM': 'Consumer Defensive',\n",
       " 'PNRA': 'Unknown',\n",
       " 'PRGO': 'Healthcare',\n",
       " 'PXD': 'Energy',\n",
       " 'PYPL': 'Financial Services',\n",
       " 'QCOM': 'Technology',\n",
       " 'RAI': 'Unknown',\n",
       " 'REGN': 'Healthcare',\n",
       " 'SBUX': 'Consumer Cyclical',\n",
       " 'SCHW': 'Financial Services',\n",
       " 'SLB': 'Energy',\n",
       " 'SNI': 'Unknown',\n",
       " 'SRPT': 'Healthcare',\n",
       " 'STJ': 'Unknown',\n",
       " 'SYF': 'Financial Services',\n",
       " 'T': 'Communication Services',\n",
       " 'TDG': 'Industrials',\n",
       " 'TGT': 'Consumer Defensive',\n",
       " 'TJX': 'Consumer Cyclical',\n",
       " 'TSLA': 'Consumer Cyclical',\n",
       " 'TWTR': 'Unknown',\n",
       " 'TWX': 'Unknown',\n",
       " 'TXN': 'Technology',\n",
       " 'UNH': 'Healthcare',\n",
       " 'UNP': 'Industrials',\n",
       " 'UPS': 'Industrials',\n",
       " 'USB': 'Financial Services',\n",
       " 'VRX': 'Unknown',\n",
       " 'VZ': 'Communication Services',\n",
       " 'WBA': 'Healthcare',\n",
       " 'WDC': 'Technology',\n",
       " 'WFC': 'Financial Services',\n",
       " 'WFM': 'Unknown',\n",
       " 'WMT': 'Consumer Defensive'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(sector_map)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3636931",
   "metadata": {},
   "source": [
    "Get historical price data for SPY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e6034a26",
   "metadata": {},
   "outputs": [],
   "source": [
    "spy = obb.equity.price.historical(\n",
    "    \"SPY\",\n",
    "    start_date=returns.index.min(),\n",
    "    end_date=returns.index.max(),\n",
    "    provider=\"yfinance\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c636ca50",
   "metadata": {},
   "source": [
    "Convert the index to datetime and calculate the benchmark returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bc4005d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "spy.index = pd.to_datetime(spy.index)\n",
    "benchmark_returns = spy.close.pct_change()\n",
    "benchmark_returns.name = \"SPY\"\n",
    "benchmark_returns = benchmark_returns.tz_localize(\"UTC\").filter(returns.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d34f4853",
   "metadata": {},
   "source": [
    "Display the benchmark returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "df17a8e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-01-04 00:00:00+00:00         NaN\n",
       "2016-01-05 00:00:00+00:00    0.001691\n",
       "2016-01-06 00:00:00+00:00   -0.012614\n",
       "2016-01-07 00:00:00+00:00   -0.023992\n",
       "2016-01-08 00:00:00+00:00   -0.010977\n",
       "                               ...   \n",
       "2017-12-22 00:00:00+00:00   -0.000262\n",
       "2017-12-26 00:00:00+00:00   -0.001196\n",
       "2017-12-27 00:00:00+00:00    0.000487\n",
       "2017-12-28 00:00:00+00:00    0.002057\n",
       "2017-12-29 00:00:00+00:00   -0.003771\n",
       "Name: SPY, Length: 503, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(benchmark_returns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4ff9910",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08b7dcad-51f2-4566-9b56-ef3d69c27dc8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
